# encoding:utf-8
import pymysql
from pymysql.constants import CLIENT
from warnings import filterwarnings
from utils.file_handling.log_handling import logger


# 忽略 Mysql 告警信息
filterwarnings("ignore", category=pymysql.Warning)

class Mysql:

    def __init__(self, host, username, password, db, charset='utf8', port=3306):
        try:
            # 建立数据库连接
            self.conn = pymysql.connect(host=host, port=port, user=username,
                                        password=password,
                                        db=db, charset=charset, client_flag=CLIENT.MULTI_STATEMENTS)

            # 使用 cursor 方法获取操作游标，得到一个可以执行sql语句，并且操作结果为字典返回的游标
            self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        except AttributeError as error:
            logger.error("数据库连接失败，失败原因 %s", error)

    def __del__(self):
        try:
            # 关闭游标
            self.cursor.close()
            # 关闭连接
            self.conn.close()
        except AttributeError as error:
            logger.error("数据库连接失败，失败原因 %s", error)

    # 查（单个）
    def query(self, sql, params=(), state="all"):
        """
            查询
            :param sql:
            :param state:  all 是默认查询全部
            :return:
            """
        try:
            self.cursor.execute(sql,params)

            if state == "all":
                # 查询全部
                data = self.cursor.fetchall()
            else:
                # 查询单条
                data = self.cursor.fetchone()
            return data
        except AttributeError as error_data:
            logger.error("数据库连接失败，失败原因 %s", error_data)
            raise

    # 增
    def insert(self, sql, params=()):
        return self.__edit(sql, params)

    # 改
    def update(self, sql, params=()):
        return self.__edit(sql, params)

    # 删
    def delete(self, sql, params=()):
        return self.__edit(sql, params)

    def __edit(self, sql, params):
        '''
        INSERT INTO new_commoditylist(
                        trade_name,common_name,specifications,commodity_type,manufacturer,retail_price
                        )values(%s,%s,%s,%s,%s,%s)
        params:()
        '''
        try:
            count = self.cursor.execute(sql, params)
            self.conn.commit()
            return count
        except AttributeError as error:
            logger.error("数据库连接失败，失败原因 %s", error)
            # 如果事务异常，则回滚数据
            self.conn.rollback()
            raise

    def insertMany(self, sql, params=()):
        return self.__editMany(sql, params)

    # 改
    def updateMany(self, sql, params=()):
        return self.__editMany(sql, params)

    # 删
    def deleteMany(self, sql, params=()):
        return self.__editMany(sql, params)

    def __editMany(self, sql, params):
        '''
        支持同时执行多条sql语句
        INSERT INTO new_commoditylist(
                        trade_name,common_name,specifications,commodity_type,manufacturer,retail_price
                        )values(%s,%s,%s,%s,%s,%s)
        params:元组列表 [(),()]
        '''
        try:
            count = self.cursor.executemany(sql, params)
            self.conn.commit()
            return count
        except AttributeError as error:
            logger.error("数据库连接失败，失败原因 %s", error)
            # 如果事务异常，则回滚数据
            self.conn.rollback()
            raise



if __name__ == '__main__':
    sql = f"""select * from test1"""
    sql1 = f"""insert into test1(name,age)values('zhu',10)"""

    result = Mysql('139.224.199.193', 'root', 'Yzp&zhx20191026,./', 'automation', ).query(sql)
    print(result)
    # # Mysql('139.224.199.193', 'root', 'Yzp&zhx20191026,./', 'automation', ).insert(sql1)
    # sql2 = f"""update test1 set name='yannn' where id=1"""
    # Mysql('139.224.199.193', 'root', 'Yzp&zhx20191026,./', 'automation', ).update(sql2,[('name', '')])


